package ctrip.etl

import ctrip.common.LineParser
import ctrip.utils.TimeUtils
import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkConf, SparkContext}

import scala.util.control.Breaks

/**
  * 清洗训练数据
 * 1.清除null空值，除user_confirmtime以外的负值
 * 2.特征提取
 * Created by gongenbo on 2017/5/15.
 */
object CtripOrderETL {

  def main(args: Array[String]): Unit = {
    var limit: Int = 1000

    val sparkConf = new SparkConf().setAppName("CtripOrderForecast")/*.setMaster("local")*/.set("spark.streaming.unpersist", "true")
      .set("spark.shuffle.consolidateFiles", "true").set("spark.default.parallelism", "1").set("spark.sql.shuffle.partitions", "1");
    /*
    val sparkConf = new SparkConf().setAppName("CtripOrderForecast").setMaster("spark://175.102.18.112:7077")
    */
      /*.set("spark.streaming.unpersist", "true")
      .set("spark.shuffle.consolidateFiles", "true").set("spark.default.parallelism", "1").set("spark.sql.shuffle.partitions", "1");*/

    val sc = new SparkContext(sparkConf)
    //val sqlContext = new SQLContext(sc)
    //加载训练数据
    /*
    val df = sqlContext.load("com.databricks.spark.csv",
      Map("path"->"F:\\Study\\ml\\match\\ctrip\\ctrip\\competition_train.txt",
        "header"->"true","delimiter"->"\t"))*/
    val lines=sc.textFile("/data/ctrip/competition_train.txt", 1)
    //val lines=sc.textFile("F:\\Study\\ml\\match\\ctrip\\ctrip\\competition_train.txt", 1)
    //val rdd=lines.map(_.split("\t"))

    val rowRdd=lines.filter(lines=>{
        var flag:Boolean=true
        var i=0
        val line=lines.split("\t")
        val loop = new Breaks

        loop.breakable {
          while(i<157){
            //过滤掉第一行字段名
            //过滤除了user_confirmtime以外所有值为负数的数据
            if((line(i).toString.startsWith("-")&&i!=27)/*||line(0).equals("orderid")*/){
              flag=false
              loop.break
            }
            i=i+1
          }
        }
        flag
    })

    /**
     * 第三列：时间拆成 时间戳，是否节假日
     *
     */
    println("*************************************开始进行map")
    val etl=rowRdd.map(lines=>{
      val line=lines.split("\t")
      var i=0;
      //如果为空，设为零
      while(i<line.length){
        if(line(i).equalsIgnoreCase("null")){
          line(i)="0"
        }
        //如果为负数，取绝对值
        if(line(i).startsWith("-")){
          line(i)=line(i).substring(1)
        }
        i=i+1;
      }
      //太low了

      val lineName: Array[String] = "orderid\tuid\torderdate\thotelid\tbasicroomid\troomid\torderlabel\tstar\trank\treturnvalue\tprice_deduct\tbasic_minarea\tbasic_maxarea\troomservice_1\troomservice_2\troomservice_3\troomservice_4\troomservice_5\troomservice_6\troomservice_7\troomservice_8\troomtag_1\troomtag_2\troomtag_3\troomtag_4\troomtag_5\troomtag_6\tuser_confirmtime\tuser_avgadvanceddate\tuser_avgstar\tuser_avggoldstar\tuser_avgrecommendlevel\tuser_avgroomnum\tordertype_1_ratio\tordertype_2_ratio\tordertype_3_ratio\tordertype_4_ratio\tordertype_5_ratio\tordertype_6_ratio\tordertype_7_ratio\tordertype_8_ratio\tordertype_9_ratio\tordertype_10_ratio\tordertype_11_ratio\tuser_avgdealpriceholiday\tuser_avgdealpriceworkday\tuser_avgdealprice\tuser_avgpromotion\tuser_avgprice_star\torderbehavior_1_ratio\torderbehavior_2_ratio\torderbehavior_3_ratio_1week\torderbehavior_4_ratio_1week\torderbehavior_5_ratio_1week\torderbehavior_3_ratio_1month\torderbehavior_4_ratio_1month\torderbehavior_5_ratio_1month\torderbehavior_3_ratio_3month\torderbehavior_4_ratio_3month\torderbehavior_5_ratio_3month\torderbehavior_6_ratio\torderbehavior_7_ratio\torderbehavior_8\torderbehavior_9\tuser_ordernum\tuser_activation\tuser_avgprice\tuser_maxprice\tuser_minprice\tuser_stdprice\tuser_cvprice\tuser_citynum\tuser_avgroomarea\tuser_roomservice_4_0ratio\tuser_roomservice_4_2ratio\tuser_roomservice_4_3ratio\tuser_roomservice_4_4ratio\tuser_roomservice_4_1ratio\tuser_roomservice_4_5ratio\tuser_roomservice_3_123ratio\tuser_roomservice_6_2ratio\tuser_roomservice_6_1ratio\tuser_roomservice_6_0ratio\tuser_roomservice_5_1ratio\tuser_roomservice_7_0ratio\tuser_roomservice_2_1ratio\tuser_roomservice_8_1ratio\tuser_rank_ratio\tuser_roomservice_5_345ratio\tuser_ordnum_1week\tuser_avgprice_1week\tuser_medprice_1week\tuser_minprice_1week\tuser_maxprice_1week\tuser_roomservice_3_123ratio_1week\tuser_roomservice_7_1ratio_1week\tuser_roomservice_7_0ratio_1week\tuser_roomservice_4_5ratio_1week\tuser_roomservice_4_4ratio_1week\tuser_roomservice_4_2ratio_1week\tuser_roomservice_4_3ratio_1week\tuser_roomservice_4_0ratio_1week\tuser_ordnum_1month\tuser_avgprice_1month\tuser_medprice_1month\tuser_minprice_1month\tuser_maxprice_1month\tuser_roomservice_3_123ratio_1month\tuser_roomservice_7_1ratio_1month\tuser_roomservice_7_0ratio_1month\tuser_roomservice_4_5ratio_1month\tuser_roomservice_4_4ratio_1month\tuser_roomservice_4_2ratio_1month\tuser_roomservice_4_3ratio_1month\tuser_roomservice_4_0ratio_1month\tuser_ordnum_3month\tuser_avgprice_3month\tuser_medprice_3month\tuser_minprice_3month\tuser_maxprice_3month\tuser_roomservice_3_123ratio_3month\tuser_roomservice_7_1ratio_3month\tuser_roomservice_7_0ratio_3month\tuser_roomservice_4_5ratio_3month\tuser_roomservice_4_4ratio_3month\tuser_roomservice_4_2ratio_3month\tuser_roomservice_4_3ratio_3month\tuser_roomservice_4_0ratio_3month\tbasic_week_ordernum_ratio\tbasic_recent3_ordernum_ratio\tbasic_comment_ratio\tbasic_30days_ordnumratio\tbasic_30days_realratio\troom_30days_ordnumratio\troom_30days_realratio\torderid_lastord\torderdate_lastord\thotelid_lastord\troomid_lastord\tbasicroomid_lastord\trank_lastord\treturn_lastord\tprice_last_lastord\troomservice_2_lastord\troomservice_3_lastord\troomservice_4_lastord\troomservice_5_lastord\troomservice_6_lastord\troomservice_8_lastord\troomtag_2_lastord\troomtag_3_lastord\troomtag_4_lastord\troomtag_5_lastord\troomtag_6_lastord\tstar_lastord\thotel_minprice_lastord\tbasic_minprice_lastord".split("\t")
      //通过字段名与行映射出Key/value键值对
      val cMap: Map[String, String] = LineParser.parseLine(lineName,line)
      //是否工作日
      var holidayLabel=1;
      if(cMap.getOrElse("orderdate","0").equals("2013-04-14")||cMap.getOrElse("orderdate","0").equals("2013-04-13")||cMap.getOrElse("orderdate","0").equals("2013-04-20")||cMap.getOrElse("orderdate","0").equals("2013-04-21")){
        holidayLabel=0;
      }
      //是否订购了
      val orderLabel=line(6);
      //酒店级别
      val star=line(7)
      val rank=line(8)
      val returnvalue=line(9)
      val roomservice_1=line(13)
      val roomservice_2=line(14)
      val roomservice_3=line(15)
      val roomservice_4=line(16)
      val roomservice_5=line(17)
      val roomservice_6=line(18)
      val roomservice_7=line(19)
      val roomservice_8=line(20)
      val roomtag_1=line(21)
      val roomtag_2=line(22)
      val roomtag_3=line(23)
      val roomtag_4=line(24)
      val roomtag_5=line(25)
      val roomtag_6=line(26)
      //空值补平均值
      var orderbehavior_2_ratio=cMap.getOrElse("orderbehavior_2_ratio","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_2_ratio)){
        orderbehavior_2_ratio="0.40610622293116866"
      }
      var orderbehavior_3_ratio_1week=cMap.getOrElse("orderbehavior_3_ratio_1week","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_3_ratio_1week)){
        orderbehavior_3_ratio_1week="0.37976724468292966"
      }
      var orderbehavior_4_ratio_1week=cMap.getOrElse("orderbehavior_4_ratio_1week","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_4_ratio_1week)){
        orderbehavior_4_ratio_1week="0.22033559110881612"
      }
      var orderbehavior_5_ratio_1week=cMap.getOrElse("orderbehavior_5_ratio_1week","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_5_ratio_1week)){
        orderbehavior_5_ratio_1week="0.31964054956361193"
      }
      var orderbehavior_3_ratio_3month=cMap.getOrElse("orderbehavior_3_ratio_3month","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_3_ratio_3month)){
        orderbehavior_3_ratio_3month="0.3604068560294937"
      }
      var orderbehavior_4_ratio_3month=cMap.getOrElse("orderbehavior_4_ratio_3month","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_4_ratio_3month)){
        orderbehavior_4_ratio_3month="0.3604068560294937"
      }
      var orderbehavior_5_ratio_3month=cMap.getOrElse("orderbehavior_5_ratio_3month","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_5_ratio_3month)){
        orderbehavior_5_ratio_3month="0.3604068560294937"
      }
      var orderbehavior_6_ratio=cMap.getOrElse("orderbehavior_6_ratio","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_6_ratio)){
        orderbehavior_6_ratio="0.2555021668226835"
      }
      var orderbehavior_7_ratio=cMap.getOrElse("orderbehavior_7_ratio","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_7_ratio)){
        orderbehavior_7_ratio="0.4388145261047945"
      }
      var orderbehavior_8=cMap.getOrElse("orderbehavior_8","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_8)){
        orderbehavior_8="51.92068267414109"
      }
      var orderbehavior_9=cMap.getOrElse("orderbehavior_9","0")+""
      if("NULL".equalsIgnoreCase(orderbehavior_9)){
        orderbehavior_9="93.96930814273199"
      }
      var user_roomservice_4_0ratio=cMap.getOrElse("user_roomservice_4_0ratio","0")+""
      if("NULL".equalsIgnoreCase(user_roomservice_4_0ratio)){
        user_roomservice_4_0ratio="0.19080784572471643"
      }
      var user_roomservice_4_1ratio=cMap.getOrElse("user_roomservice_4_1ratio","0")+""
      if("NULL".equalsIgnoreCase(user_roomservice_4_1ratio)){
        user_roomservice_4_1ratio="0.19080784572471643"
      }
      var user_roomservice_4_5ratio=cMap.getOrElse("user_roomservice_4_5ratio","0")+""
      if("NULL".equalsIgnoreCase(user_roomservice_4_5ratio)){
        user_roomservice_4_5ratio="0.02682172667873226"
      }
      var user_roomservice_6_2ratio=cMap.getOrElse("user_roomservice_6_2ratio","0")+""
      if("NULL".equalsIgnoreCase(user_roomservice_6_2ratio)){
        user_roomservice_6_2ratio="0.328697987333435"
      }
      var user_roomservice_6_1ratio=cMap.getOrElse("user_roomservice_6_1ratio","0")+""
      if("NULL".equalsIgnoreCase(user_roomservice_6_1ratio)){
        user_roomservice_6_1ratio="0.11134549206621581"
      }
      var user_roomservice_6_0ratio=cMap.getOrElse("user_roomservice_6_0ratio","0")+""
      if("NULL".equalsIgnoreCase(user_roomservice_6_0ratio)){
        user_roomservice_6_0ratio="0.5474127655681356"
      }
      var user_roomservice_2_1ratio=cMap.getOrElse("user_roomservice_2_1ratio","0")+""
      if("NULL".equalsIgnoreCase(user_roomservice_2_1ratio)){
        user_roomservice_2_1ratio="0.17339320766332583"
      }
      var user_rank_ratio=cMap.getOrElse("user_rank_ratio","0")+""
      if("NULL".equalsIgnoreCase(user_rank_ratio)){
        user_rank_ratio="5.25811654955762"
      }
      //空值取平均
      var basic_week_ordernum_ratio=cMap.getOrElse("basic_week_ordernum_ratio","0")+""
      if("NULL".equalsIgnoreCase(basic_week_ordernum_ratio)){
        basic_week_ordernum_ratio="0.19080784572471643"
      }
      var basic_recent3_ordernum_ratio=cMap.getOrElse("basic_recent3_ordernum_ratio","0")+""
      if("NULL".equalsIgnoreCase(basic_recent3_ordernum_ratio)){
        basic_recent3_ordernum_ratio="0.18891273128300484"
      }
      var basic_comment_ratio=cMap.getOrElse("basic_comment_ratio","0")+""
      if("NULL".equalsIgnoreCase(basic_comment_ratio)){
        basic_comment_ratio="0.1822228233200955"
      }
      var basic_30days_ordnumratio=cMap.getOrElse("basic_30days_ordnumratio","0")+""
      if("NULL".equalsIgnoreCase(basic_30days_ordnumratio)){
        basic_30days_ordnumratio="0.23581320042718992"
      }
      var basic_30days_realratio=cMap.getOrElse("basic_30days_realratio","0")+""
      if("NULL".equalsIgnoreCase(basic_30days_realratio)){
        basic_30days_realratio="0.23581320042718992"
      }
      var room_30days_ordnumratio=cMap.getOrElse("room_30days_ordnumratio","0")+""
      if("NULL".equalsIgnoreCase(room_30days_ordnumratio)){
        room_30days_ordnumratio="0.05936138604162129"
      }
      var room_30days_realratio=cMap.getOrElse("room_30days_realratio","0")+""
      if("NULL".equalsIgnoreCase(room_30days_realratio)){
        room_30days_realratio="1.3482919613570066"
      }
      //处理标黄的标签直接添加

      holidayLabel+"\t"+star+"\t"+rank+"\t"+returnvalue+"\t"+roomservice_1+"\t"+roomservice_2+"\t"+roomservice_3+"\t"+roomservice_4+"\t"+roomservice_5+"\t"+roomservice_6+"\t"+roomservice_7+"\t"+roomservice_8+"\t"+roomtag_1+"\t"+roomtag_2+"\t"+roomtag_3+"\t"+roomtag_4+"\t"+roomtag_5+"\t"+roomtag_6+"\t"+cMap.getOrElse("user_confirmtime",0)+""+"\t"+cMap.getOrElse("user_avgadvanceddate",0)+""+"\t"+cMap.getOrElse("user_avgstar",0)+""+"\t"+cMap.getOrElse("user_avggoldstar",0)+""+"\t"+cMap.getOrElse("user_avgrecommendlevel",0)+""+"\t"+cMap.getOrElse("user_avgroomnum",0)+""+"\t"+cMap.getOrElse("ordertype_1_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_2_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_3_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_4_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_5_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_6_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_7_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_8_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_9_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_10_ratio",0)+""+"\t"+cMap.getOrElse("ordertype_11_ratio",0)+""+"\t"+cMap.getOrElse("user_avgdealpriceholiday",0)+""+"\t"+cMap.getOrElse("user_avgdealpriceworkday",0)+""+"\t"+cMap.getOrElse("user_avgdealprice",0)+""+"\t"+cMap.getOrElse("user_avgpromotion",0)+""+"\t"+cMap.getOrElse("user_avgprice_star",0)+""+"\t"+cMap.getOrElse("orderbehavior_1_ratio",0)+""+"\t"+orderbehavior_2_ratio+"\t"+orderbehavior_3_ratio_1week+"\t"+orderbehavior_4_ratio_1week+"\t"+orderbehavior_5_ratio_1week+"\t"+cMap.getOrElse("orderbehavior_3_ratio_1month",0)+""+"\t"+cMap.getOrElse("orderbehavior_4_ratio_1month",0)+""+"\t"+cMap.getOrElse("orderbehavior_5_ratio_1month",0)+""+"\t"+orderbehavior_3_ratio_3month+"\t"+orderbehavior_4_ratio_3month+"\t"+orderbehavior_5_ratio_3month+"\t"+orderbehavior_6_ratio+"\t"+orderbehavior_7_ratio+"\t"+orderbehavior_8+"\t"+orderbehavior_9+"\t"+cMap.getOrElse("user_ordernum",0)+""+"\t"+cMap.getOrElse("user_activation",0)+""+"\t"+cMap.getOrElse("user_avgprice",0)+""+"\t"+cMap.getOrElse("user_maxprice",0)+""+"\t"+cMap.getOrElse("user_minprice",0)+""+"\t"+cMap.getOrElse("user_citynum",0)+""+"\t"+cMap.getOrElse("user_avgroomarea",0)+""+"\t"+user_roomservice_4_0ratio+"\t"+cMap.getOrElse("user_roomservice_4_2ratio",0)+""+"\t"+cMap.getOrElse("user_roomservice_4_3ratio",0)+""+"\t"+cMap.getOrElse("user_roomservice_4_4ratio",0)+""+"\t"+user_roomservice_4_1ratio+"\t"+user_roomservice_4_5ratio+"\t"+cMap.getOrElse("user_roomservice_3_123ratio",0)+""+"\t"+user_roomservice_6_2ratio+"\t"+user_roomservice_6_1ratio+"\t"+user_roomservice_6_0ratio+"\t"+cMap.getOrElse("user_roomservice_5_1ratio",0)+""+"\t"+cMap.getOrElse("user_roomservice_7_0ratio",0)+""+"\t"+user_roomservice_2_1ratio+"\t"+cMap.getOrElse("user_roomservice_8_1ratio",0)+""+"\t"+user_rank_ratio+"\t"+cMap.getOrElse("user_roomservice_5_345ratio",0)+""+"\t"+cMap.getOrElse("user_ordnum_1week",0)+""+"\t"+basic_week_ordernum_ratio+"\t"+basic_recent3_ordernum_ratio+"\t"+basic_comment_ratio+"\t"+basic_30days_ordnumratio+"\t"+basic_30days_realratio+"\t"+room_30days_ordnumratio+"\t"+room_30days_realratio+"\t"++orderLabel+"\t"+cMap.getOrElse("orderid",0)+""
    })
    //println("lines count:"+lines.count()+"etl count:"+etl.count())
    //etl.saveAsTextFile("F:\\Study\\ml\\match\\ctrip\\ctrip\\etl_competition_train5")
    etl.saveAsTextFile("/mnt/disk/data/ctrip/etl_feature")
  }
}
